<?php

namespace app\admin\controller;
use app\admin\model\bin\History;
use app\common\controller\Backend;
use app\admin\library\Auth;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use think\exception\PDOException;
use  app\admin\model;
use think\Session;
use app\admin\model\Bin;
use app\admin\model\order\Rent;
use app\admin\model\order\Sell as OrderSell;

/**
 * 迁移管理
 *
 * @icon fa fa-circle-o
 */
class Migrate extends Backend
{

    /**
     * Test模型对象
     * @var \app\admin\model\Test
     */
    protected $model = null;
    protected $admin = null;
    protected $organization = null;
    protected $bin_type = null;
    protected $purchase = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\Bin();
        $this->admin = new \app\admin\model\Admin;
        $this->organization = new \app\admin\model\Organization;
        $this->bin_type = new \app\admin\model\bin\Type;
        $this->purchase = new \app\admin\model\order\Purchase;

    }



    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */

    /**
     * 导入箱计划-入场
     *
     * @return void
     * @throws PDOException
     * @throws BindParamException
     */

    public function import_plan()
    {
        $file = $this->request->request('file');
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, 'w');
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding !== 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
        $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';

        $table = $this->purchase->getQuery()->getTable();
        $database = \think\Config::get('database.database');
        $fieldArr = [];
        $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
        foreach ($list as $k => $v) {
            if ($importHeadType == 'comment') {
                $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
            } else {
                $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }
        }

        //加载文件
        $insert = [];
        try {
            if (!$PHPExcel = $reader->load($filePath)) {
                $this->error(__('Unknown data format'));
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
            $fields = [];
            for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $fields[] = trim($val);
                }
            }

            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $values[] = is_null($val) ? '' : $val;
                }
                $row = [];
                $temp = array_combine($fields, $values);
                foreach ($temp as $k => $v) {
                    if (isset($fieldArr[$k]) && $k !== '') {
                        $row[$fieldArr[$k]] = $v;
                    }
                }
                if ($row) {
                    if(!empty($row['purchasetime'])){
                        $row['purchasetime']=excelToTime($row['purchasetime']);
                    }
                    if($row['take_number']!='退组' && $row['take_number']!='退租' && substr($row['id'],0,2)!='PL'){
                        $insert[] = $row;
                    }
                }
            }
        } catch (Exception $exception) {
            $this->error($exception->getMessage());
        }
        if (!$insert) {
            $this->error(__('No rows were updated'));
        }

        try {
            //是否包含admin_id字段
          /*  $has_admin_id = false;
            foreach ($fieldArr as $name => $key) {
                if ($key == 'admin_id') {
                    $has_admin_id = true;
                    break;
                }
            }
            if ($has_admin_id) {
                $auth = Auth::instance();
                foreach ($insert as &$val) {
                    if (!isset($val['admin_id']) || empty($val['admin_id'])) {
                        $val['admin_id'] = $auth->isLogin() ? $auth->id : 0;
                    }
                }
            }*/
            foreach ($insert as &$val) {
                $val['yard_id']=3; //默认值需调整
                $val['bin_native']=1; //默认值需调整
                $val['our_company_id']=2; //默认值需调整
                $val['unit_price']=0; //默认值需调整
               if($val['purchase_type']=='进场'){
                   $val['purchase_type']=1;
               }
                if($val['complete_number']>=$val['purchase_number']){
                    $val['is_finish']=1;
                }

                if(!empty($val['remark'])){
                    $remarkData['correspond_id'] = $val['id'];
                    $remarkData['type'] = 1;
                    $remarkData['user_id'] = Session::get('admin.id');
                    $remarkData['remark_content'] = $val['remark'];
                    $remarkData['createtime'] = $val['purchasetime'];
                    db('remark')->insert($remarkData);
                }
                $val['createtime']=$val['purchasetime'];

                $val['purchase_sn']='P'.substr(md5(microtime(true)), 0, 9);
                $salesman_id = $this->admin->where(['nickname' => $val['salesman_id']])->value('id');
                if(!$salesman_id){
                    $adminData['username']= $val['salesman_id'];
                    $adminData['nickname']= $val['salesman_id'];
                    $adminData['password']= '0c7bc9108887de33a1a3488ef3543028';
                    $adminData['salt']='c3aa71';
                    $adminData['createtime']=time();
                    $salesman_id=$this->admin->insertGetId($adminData);
                }
                $val['salesman_id']=$salesman_id;

                $bin_company_id = $this->organization->where(['organ_name' => $val['bin_company_id'],'organ_type'=>3])->value('id');
                if(!$bin_company_id){
                    $organData['organ_name']= $val['bin_company_id'];
                    $organData['organ_short_name']= $val['bin_company_id'];
                    $organData['organ_type']= 3;
                    $organData['organ_sn']='O'.substr(md5(microtime(true)), 0, 9);
                    $organData['createtime']=time();
                    $organData['organ_city']='中国';
                    $bin_company_id=$this->organization->insertGetId($organData);
                }
                $val['bin_company_id']=$bin_company_id;

                $val['bin_type']=$val['weigh'].$val['bin_type'];
                $val['weigh']=0;
                $bin_type = $this->bin_type->where(['name' => $val['bin_type']])->value('id');
                if(!$bin_type){
                    $typeData['name']= $val['bin_type'];
                    $typeData['createtime']=time();
                    $bin_type=$this->bin_type->insertGetId($typeData);
                }
                $val['bin_type']=$bin_type;
            }
            $this->purchase->saveAll($insert,false);
        } catch (PDOException $exception) {
            $msg = $exception->getMessage();
            if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
                $msg = "导入失败，包含【{$matches[1]}】的记录已存在";
            };
            $this->error($msg);
        } catch (Exception $e) {
            $this->error($e->getMessage());
        }

        $this->success();
    }

    /**
     * 导入箱计划-出场
     *
     * @return void
     * @throws PDOException
     * @throws BindParamException
     */

    public function import_plan_out()
    {
        $orderSell=new OrderSell();
        $file = $this->request->request('file');
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, 'w');
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding !== 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
        $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';

        $table = $orderSell->getQuery()->getTable();
        $database = \think\Config::get('database.database');
        $fieldArr = [];
        $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
        foreach ($list as $k => $v) {
            if ($importHeadType == 'comment') {
                $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
            } else {
                $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }
        }

        //加载文件
        $insert = [];
        try {
            if (!$PHPExcel = $reader->load($filePath)) {
                $this->error(__('Unknown data format'));
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
            $fields = [];
            for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $fields[] = trim($val);
                }
            }

            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $values[] = is_null($val) ? '' : $val;
                }
                $row = [];
                $temp = array_combine($fields, $values);
                foreach ($temp as $k => $v) {
                    if (isset($fieldArr[$k]) && $k !== '') {
                        $row[$fieldArr[$k]] = $v;
                    }
                }

                if ($row) {
                    if(!empty($row['selltime'])){
                        $row['selltime']=excelToTime($row['selltime']);
                    }
                    if(substr($row['id'],0,2)=='PL'){
                        $insert[] = $row;
                    }
                }
            }
        } catch (Exception $exception) {
            $this->error($exception->getMessage());
        }
        if (!$insert) {
            $this->error(__('No rows were updated'));
        }

        try {
            //是否包含admin_id字段
            /*  $has_admin_id = false;
              foreach ($fieldArr as $name => $key) {
                  if ($key == 'admin_id') {
                      $has_admin_id = true;
                      break;
                  }
              }
              if ($has_admin_id) {
                  $auth = Auth::instance();
                  foreach ($insert as &$val) {
                      if (!isset($val['admin_id']) || empty($val['admin_id'])) {
                          $val['admin_id'] = $auth->isLogin() ? $auth->id : 0;
                      }
                  }
              }*/
            foreach ($insert as $k=>&$val) {
                $val['sell_sn']=$val['id'];
                $val['id']=41000+$k;
                $selltime=$val['selltime'];
                $val['take_yard_id']=3; //默认值需调整
                $val['bin_native']=1; //默认值需调整
                $val['bin_origin']=1; //默认值需调整
                $val['collect_company_id']=0; //默认值需调整
                $val['unit_price']=0; //默认值需调整
               /* if($val['purchase_type']=='进场'){
                    $val['purchase_type']=1;
                }*/
                if($val['complete_number']>=$val['sell_number']){
                    $val['is_finish']=1;
                }

                if(!empty($val['remark'])){
                    $remarkData['correspond_id'] = $val['id'];
                    $remarkData['type'] = 2;
                    $remarkData['user_id'] = Session::get('admin.id');
                    $remarkData['remark_content'] = $val['remark'];
                    $remarkData['createtime'] =$selltime;
                    db('remark')->insert($remarkData);
                }
                $val['createtime']=$selltime;

/*                $val['sell_sn']='SE'.substr(md5(microtime(true)), 0, 9);*/
                $salesman_id = $this->admin->where(['nickname' => $val['salesman_id']])->value('id');
                if(!$salesman_id){
                    $adminData['username']= $val['salesman_id'];
                    $adminData['nickname']= $val['salesman_id'];
                    $adminData['password']= '0c7bc9108887de33a1a3488ef3543028';
                    $adminData['salt']='c3aa71';
                    $adminData['createtime']=time();
                    $salesman_id=$this->admin->insertGetId($adminData);
                }
                $val['salesman_id']=$salesman_id;

              /*  $bin_company_id = $this->organization->where(['organ_name' => $val['bin_company_id'],'organ_type'=>3])->value('id');
                if(!$bin_company_id){
                    $organData['organ_name']= $val['bin_company_id'];
                    $organData['organ_short_name']= $val['bin_company_id'];
                    $organData['organ_type']= 3;
                    $organData['organ_sn']='O'.substr(md5(microtime(true)), 0, 9);
                    $organData['createtime']=time();
                    $organData['organ_city']='中国';
                    $bin_company_id=$this->organization->insertGetId($organData);
                }
                $val['bin_company_id']=$bin_company_id;*/

                $val['bin_type']=$val['weigh'].$val['bin_type'];
                $val['weigh']=0;
                $bin_type = $this->bin_type->where(['name' => $val['bin_type']])->value('id');
                if(!$bin_type){
                    $typeData['name']= $val['bin_type'];
                    $typeData['createtime']=time();
                    $bin_type=$this->bin_type->insertGetId($typeData);
                }
                $val['bin_type']=$bin_type;
            }
            $orderSell->saveAll($insert,false);
        } catch (PDOException $exception) {
            $msg = $exception->getMessage();
            if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
                $msg = "导入失败，包含【{$matches[1]}】的记录已存在";
            };
            $this->error($msg);
        } catch (Exception $e) {
            $this->error($e->getMessage());
        }

        $this->success();
    }

    /**
     * 导入箱计划-出租
     *
     * @return void
     * @throws PDOException
     * @throws BindParamException
     */

    public function import_plan_rent()
    {
        $rent=new Rent();
        $file = $this->request->request('file');
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, 'w');
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding !== 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
        $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';

        $table = $rent->getQuery()->getTable();
        $database = \think\Config::get('database.database');
        $fieldArr = [];
        $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
        foreach ($list as $k => $v) {
            if ($importHeadType == 'comment') {
                $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
            } else {
                $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }
        }

        //加载文件
        $insert = [];
        try {
            if (!$PHPExcel = $reader->load($filePath)) {
                $this->error(__('Unknown data format'));
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
            $fields = [];
            for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $fields[] = trim($val);
                }
            }

            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $values[] = is_null($val) ? '' : $val;
                }
                $row = [];
                $temp = array_combine($fields, $values);
                foreach ($temp as $k => $v) {
                    if (isset($fieldArr[$k]) && $k !== '') {
                        $row[$fieldArr[$k]] = $v;
                    }
                }
                if ($row) {
                    $insert[] = $row;
                }
            }
        } catch (Exception $exception) {
            $this->error($exception->getMessage());
        }
        if (!$insert) {
            $this->error(__('No rows were updated'));
        }

        try {
            //是否包含admin_id字段
            /*  $has_admin_id = false;
              foreach ($fieldArr as $name => $key) {
                  if ($key == 'admin_id') {
                      $has_admin_id = true;
                      break;
                  }
              }
              if ($has_admin_id) {
                  $auth = Auth::instance();
                  foreach ($insert as &$val) {
                      if (!isset($val['admin_id']) || empty($val['admin_id'])) {
                          $val['admin_id'] = $auth->isLogin() ? $auth->id : 0;
                      }
                  }
              }*/
            foreach ($insert as &$val) {
                $val['take_yard_id']=15; //默认值需调整
                $val['bin_native']=1; //默认值需调整
                $val['bin_origin']=1; //默认值需调整
                /* if($val['purchase_type']=='进场'){
                     $val['purchase_type']=1;
                 }*/
                if($val['complete_number']>=$val['rent_number']){
                    $val['is_finish']=1;
                }
                if(!empty($val['renttime'])){
                    $val['renttime']=excelToTime($val['renttime']);
                }
                if(!empty($val['remark'])){
                    $remarkData['correspond_id'] = $val['id'];
                    $remarkData['type'] = 3;
                    $remarkData['user_id'] = Session::get('admin.id');
                    $remarkData['remark_content'] = $val['remark'];
                    $remarkData['createtime'] = time();
                    db('remark')->insert($remarkData);
                }
                $val['createtime']=$val['renttime'];

                $val['rent_sn']='R'.substr(md5(microtime(true)), 0, 9);

                $salesman_id = $this->admin->where(['nickname' => $val['salesman_id']])->value('id');
                if(!$salesman_id){
                    $adminData['username']= $val['salesman_id'];
                    $adminData['nickname']= $val['salesman_id'];
                    $adminData['password']= '0c7bc9108887de33a1a3488ef3543028';
                    $adminData['salt']='c3aa71';
                    $adminData['createtime']=time();
                    $salesman_id=$this->admin->insertGetId($adminData);
                }
                $val['salesman_id']=$salesman_id;

                  $customer_id = $this->organization->where(['organ_name' => $val['customer_id'],'organ_type'=>4])->value('id');
                  if(!$customer_id){
                      $organData['organ_name']= $val['customer_id'];
                      $organData['organ_short_name']= $val['customer_id'];
                      $organData['organ_type']= 4;
                      $organData['organ_sn']='O'.substr(md5(microtime(true)), 0, 9);
                      $organData['createtime']=time();
                      $organData['organ_city']='中国';
                      $customer_id=$this->organization->insertGetId($organData);
                  }
                  $val['customer_id']=$customer_id;

                $val['bin_type']=$val['weigh'].$val['bin_type'];
                $val['weigh']=0;
                $bin_type = $this->bin_type->where(['name' => $val['bin_type']])->value('id');
                if(!$bin_type){
                    $typeData['name']= $val['bin_type'];
                    $typeData['createtime']=time();
                    $bin_type=$this->bin_type->insertGetId($typeData);
                }
                $val['bin_type']=$bin_type;
            }
            $rent->saveAll($insert,false);
        } catch (PDOException $exception) {
            $msg = $exception->getMessage();
            if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
                $msg = "导入失败，包含【{$matches[1]}】的记录已存在";
            };
            $this->error($msg);
        } catch (Exception $e) {
            $this->error($e->getMessage());
        }

        $this->success();
    }

    /**
     * 导入箱买箱进场
     *
     * @return void
     * @throws PDOException
     * @throws BindParamException
     */

    public function import_bin_entry()
    {
        $bin=new Bin();
        $file = $this->request->request('file');
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, 'w');
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding !== 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
        $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';

        $table = $bin->getQuery()->getTable();
        $database = \think\Config::get('database.database');
        $fieldArr = [];
        $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
        foreach ($list as $k => $v) {
            if ($importHeadType == 'comment') {
                $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
            } else {
                $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }
        }

        //加载文件
        $insert = [];
        try {
            if (!$PHPExcel = $reader->load($filePath)) {
                $this->error(__('Unknown data format'));
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
            $fields = [];
            for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $fields[] = trim($val);
                }
            }

            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $values[] = is_null($val) ? '' : $val;
                }
                $row = [];
                $temp = array_combine($fields, $values);
                foreach ($temp as $k => $v) {
                    if (isset($fieldArr[$k]) && $k !== '') {
                        $row[$fieldArr[$k]] = $v;
                    }
                }
                if ($row) {
                    if($row['take_number']!='退组' && $row['take_number']!='退租' ){
                        $insert[] = $row;
                    }
                }
            }
        } catch (Exception $exception) {
            $this->error($exception->getMessage());
        }
        if (!$insert) {
            $this->error(__('No rows were updated'));
        }

        try {
            //是否包含admin_id字段
            /*  $has_admin_id = false;
              foreach ($fieldArr as $name => $key) {
                  if ($key == 'admin_id') {
                      $has_admin_id = true;
                      break;
                  }
              }
              if ($has_admin_id) {
                  $auth = Auth::instance();
                  foreach ($insert as &$val) {
                      if (!isset($val['admin_id']) || empty($val['admin_id'])) {
                          $val['admin_id'] = $auth->isLogin() ? $auth->id : 0;
                      }
                  }
              }*/
            foreach ($insert as &$val) {

                if(substr($val['order_id'],0,2)!='PL'){
                    $val['origin_sn']=$this->purchase->where(['id'=>$val['order_id']])->value('purchase_sn');
                }elseif (substr($val['order_id'],0,2)=='PL'){
                    $val['origin_sn']=$val['order_id'];
                    $val['order_id']=$this->purchase->where(['purchase_sn'=>$val['order_id']])->value('id');
                }
                if(empty($val['origin_sn'])){
                    $val['origin_sn']='NULL';
                }
                if(empty($val['order_id'])){
                    $val['order_id']=999999;
                }
                    $val['yard_id']=3; //默认值需调整
                    $val['bin_native']=1; //默认值需调整
                    $val['bin_origin']=1; //默认值需调整
                    $val['our_company_id']=2; //默认值需调整
                    $val['current_status']=1;//现在状态:1=可用,2=调拨中,3=租出,4=卖出,5=退租
                    $val['last_status']=1;//最后状环节:1=买箱入库,2=租箱入库,3=租箱出库,4=调拨出库,5=箱子调拨,6=还箱入库,7=卖箱出库,8=退租出库
                    /* if($val['purchase_type']=='进场'){
                         $val['purchase_type']=1;
                     }*/
                    /* if($val['complete_number']>=$val['rent_number']){
                         $val['is_finish']=1;
                     }*/
                $val['remark']=$val['remark'].'     '.$val['updatetime'];

                    if(!empty($val['firstentertime'])){
                        $val['firstentertime']=excelToTime($val['firstentertime']);
                    }
                    /* if(!empty($val['remark'])){
                         $remarkData['correspond_id'] = $val['id'];
                         $remarkData['type'] = 3;
                         $remarkData['user_id'] = Session::get('admin.id');
                         $remarkData['remark_content'] = $val['remark'];
                         $remarkData['createtime'] = time();
                         db('remark')->insert($remarkData);
                     }*/
                    $val['createtime']=$val['firstentertime'];
                    $val['updatetime']=$val['firstentertime'];

                    /*                $val['rent_sn']='R'.substr(md5(microtime(true)), 0, 9);*/

                    /* $salesman_id = $this->admin->where(['nickname' => $val['salesman_id']])->value('id');
                     if(!$salesman_id){
                         $adminData['username']= $val['salesman_id'];
                         $adminData['nickname']= $val['salesman_id'];
                         $adminData['password']= '0c7bc9108887de33a1a3488ef3543028';
                         $adminData['salt']='c3aa71';
                         $adminData['createtime']=time();
                         $salesman_id=$this->admin->insertGetId($adminData);
                     }
                     $val['salesman_id']=$salesman_id;*/

                    $bin_company_id = $this->organization->where(['organ_name' => $val['bin_company_id'],'organ_type'=>3])->value('id');
                    if(!$bin_company_id){
                        $organData['organ_name']= $val['bin_company_id'];
                        $organData['organ_short_name']= $val['bin_company_id'];
                        $organData['organ_type']= 3;
                        $organData['organ_sn']='O'.substr(md5(microtime(true)), 0, 9);
                        $organData['createtime']=time();
                        $organData['organ_city']='中国';
                        $bin_company_id=$this->organization->insertGetId($organData);
                    }
                    $val['bin_company_id']=$bin_company_id;
                    /*  $customer_id = $this->organization->where(['organ_name' => $val['customer_id'],'organ_type'=>4])->value('id');
                      if(!$customer_id){
                          $organData['organ_name']= $val['customer_id'];
                          $organData['organ_short_name']= $val['customer_id'];
                          $organData['organ_type']= 4;
                          $organData['organ_sn']='O'.substr(md5(microtime(true)), 0, 9);
                          $organData['createtime']=time();
                          $organData['organ_city']='中国';
                          $customer_id=$this->organization->insertGetId($organData);
                      }
                      $val['customer_id']=$customer_id;*/

                    $val['bin_type']=$val['weigh'].$val['bin_type'];
                    $val['weigh']=100;
                    $bin_type = $this->bin_type->where(['name' => $val['bin_type']])->value('id');
                    if(!$bin_type){
                        $typeData['name']= $val['bin_type'];
                        $typeData['createtime']=time();
                        $bin_type=$this->bin_type->insertGetId($typeData);
                    }
                    $val['bin_type']=$bin_type;


            }
            $res= $bin->allowField(true)->saveAll($insert);

           if($res){
                $json = json_encode((object)$res);
                $binResLists = json_decode($json, true);
                foreach ($binResLists as $k=> &$vv){
                    $binHistoty[$k]['bin_id']=(int)$vv['id'];
                    $binHistoty[$k]['receipt']=$vv['origin_sn'];
                    $binHistoty[$k]['organization_id']=(int)$vv['our_company_id'];
                    $binHistoty[$k]['business_type']=1;
                    $binHistoty[$k]['organ_type']=2;
                    $binHistoty[$k]['yard_id']=(int)$vv['yard_id'];
                    $binHistoty[$k]['createtime']=time();

                    if(!empty($vv['firstentertime'])){
                        $binHistoty[$k]['entertime']=$vv['firstentertime'];
                        $binHistoty[$k]['createtime']=$vv['firstentertime'];
                    }

                }

            }
            $history=new History();
            $historyRes= $history->allowField(true)->saveAll($binHistoty);
        } catch (PDOException $exception) {
            $msg = $exception->getMessage();
            if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
                $msg = "导入失败，包含【{$matches[1]}】的记录已存在";
            };
            $this->error($msg);
        } catch (Exception $e) {
            $this->error($e->getMessage());
        }

        $this->success();
    }

    /**
     * 导入箱卖箱出场
     *
     * @return void
     * @throws PDOException
     * @throws BindParamException
     */

    public function import_bin_sell()
    {

        $sell=new \app\admin\model\bin\Sell();
        $orderSell=new \app\admin\model\order\Sell();

        $file = $this->request->request('file');
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, 'w');
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding !== 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
        $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';

        $table = $sell->getQuery()->getTable();
        $database = \think\Config::get('database.database');
        $fieldArr = [];
        $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
        foreach ($list as $k => $v) {
            if ($importHeadType == 'comment') {
                $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
            } else {
                $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }
        }

        //加载文件
        $insert = [];
        try {
            if (!$PHPExcel = $reader->load($filePath)) {
                $this->error(__('Unknown data format'));
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
            $fields = [];
            for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $fields[] = trim($val);
                }
            }

            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $values[] = is_null($val) ? '' : $val;
                }
                $row = [];
                $temp = array_combine($fields, $values);
                foreach ($temp as $k => $v) {
                    if (isset($fieldArr[$k]) && $k !== '') {
                        $row[$fieldArr[$k]] = $v;
                    }
                }
                if ($row) {
                    if($row['unit_price']!='租箱业务'){
                        $insert[] = $row;
                    }
                }
            }
        } catch (Exception $exception) {
            $this->error($exception->getMessage());
        }
        if (!$insert) {
            $this->error(__('No rows were updated'));
        }

        try {
            //是否包含admin_id字段
            /*  $has_admin_id = false;
              foreach ($fieldArr as $name => $key) {
                  if ($key == 'admin_id') {
                      $has_admin_id = true;
                      break;
                  }
              }
              if ($has_admin_id) {
                  $auth = Auth::instance();
                  foreach ($insert as &$val) {
                      if (!isset($val['admin_id']) || empty($val['admin_id'])) {
                          $val['admin_id'] = $auth->isLogin() ? $auth->id : 0;
                      }
                  }
              }*/
            $bin=new Bin();
            foreach ($insert as  $k =>&$val) {
                unset($val['unit_price']);
                if(substr($val['sell_order_id'],0,2)=='PL'){
                    $val['sell_order_id']=$orderSell->where(['sell_sn'=>$val['sell_order_id']])->value('id');
                }

                if(empty($val['sell_order_id'])){
                    $val['order_id']=999999;
                }
                $val['take_yard_id']=3; //默认值需调整
                $val['collect_company_id']=0; //默认值需调整
                $val['unit_price']=0; //默认值需调整
                $val['currency_type']=1; //默认值需调整
               /* $val['bin_native']=1; //默认值需调整
                $val['bin_origin']=1; //默认值需调整*/
                $val['bin_id'] = $bin->where(['bin_sn' => $val['bin_sn']])->value('id');
                if(empty($val['bin_id'])){
                    $val['bin_id']=0;
                }
                $val['bin_type'] = $bin->where(['bin_sn' => $val['bin_sn']])->value('bin_type');
                if(empty($val['bin_type'])){
                    $val['bin_type']=14;
                }
                $val['bin_origin'] = $bin->where(['bin_sn' => $val['bin_sn']])->value('bin_origin');
                if(empty($val['bin_origin'])){
                    $val['bin_origin']=1;
                }
                $val['bin_native'] = $bin->where(['bin_sn' => $val['bin_sn']])->value('bin_native');
                if(empty($val['bin_native'])){
                    $val['bin_native']=1;
                }

                if(!empty($val['selltime'])){
                    $val['selltime']=excelToTime($val['selltime']);
                }
                /* if(!empty($val['remark'])){
                     $remarkData['correspond_id'] = $val['id'];
                     $remarkData['type'] = 3;
                     $remarkData['user_id'] = Session::get('admin.id');
                     $remarkData['remark_content'] = $val['remark'];
                     $remarkData['createtime'] = time();
                     db('remark')->insert($remarkData);
                 }*/
                $val['createtime']=$val['selltime'];
                $val['take_null_time']=$val['selltime'];
                $val['remark']=$val['remark'].'     '.$val['updatetime'];
                unset($val['updatetime']);

                 $salesman_id = $this->admin->where(['nickname' => $val['salesman_id']])->value('id');
                 if(!$salesman_id){
                     $adminData['username']= $val['salesman_id'];
                     $adminData['nickname']= $val['salesman_id'];
                     $adminData['password']= '0c7bc9108887de33a1a3488ef3543028';
                     $adminData['salt']='c3aa71';
                     $adminData['createtime']=time();
                     $salesman_id=$this->admin->insertGetId($adminData);
                 }
                 $val['salesman_id']=$salesman_id;

                $val['weigh']=100;

                $binIds[$k]['id'] = $val['bin_id'];
                $binIds[$k]['current_status'] = 4;
                $binIds[$k]['last_status'] = 7;
                $binIds[$k]['state'] = 0;
                $binIds[$k]['updatetime'] = $val['take_null_time'];

                $binHistoty[$k]['take_null_time'] = $val['take_null_time'];
                $binHistoty[$k]['bin_id'] = $val['bin_id'];
                $binHistoty[$k]['business_type'] = 7;
                $binHistoty[$k]['receipt'] = $orderSell->where(['id' => $val['sell_order_id']])->value('sell_sn');
                $binHistoty[$k]['organization_id'] = $val['collect_company_id'];
                $binHistoty[$k]['organ_type'] = 5;
                $binHistoty[$k]['yard_id'] = $val['take_yard_id'];
                $binHistoty[$k]['createtime'] = $val['take_null_time'];;

            }
            $res=$sell->allowField(true)->saveAll($insert);
            $BinResult = $bin->allowField(true)->isUpdate()->saveAll($binIds);
            $history = new History();
            $historyRes = $history->allowField(true)->saveAll($binHistoty);
           /* if($res){
                $json = json_encode((object)$res);
                $binResLists = json_decode($json, true);
                foreach ($binResLists as $k=> &$vv){
                    $binHistoty[$k]['bin_id']=(int)$vv['id'];
                    $binHistoty[$k]['receipt']=$vv['origin_sn'];
                    $binHistoty[$k]['organization_id']=(int)$vv['our_company_id'];
                    $binHistoty[$k]['business_type']=1;
                    $binHistoty[$k]['organ_type']=2;
                    $binHistoty[$k]['yard_id']=(int)$vv['yard_id'];
                    if(!empty($vv['firstentertime'])){
                        $binHistoty[$k]['entertime']=$vv['firstentertime'];
                    }
                    $binHistoty[$k]['createtime']=time();

                }

            }
            $history=new History();
            $historyRes= $history->allowField(true)->saveAll($binHistoty);*/
        } catch (PDOException $exception) {
            $msg = $exception->getMessage();
            if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
                $msg = "导入失败，包含【{$matches[1]}】的记录已存在";
            };
            $this->error($msg);
        } catch (Exception $e) {
            $this->error($e->getMessage());
        }

        $this->success();
    }




}
